{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module Feedback\n",
    "\n",
    "This system records the responses of students on their learning experience at university.\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/7/71/Module-feedback.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Find the student name from a matriculation number\n",
    "\n",
    "**Find the name of the student with number 50200100**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SPR_FNM1</th>\n",
       "        <th>SPR_SURN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tom</td>\n",
       "        <td>Cotton</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Tom', 'Cotton')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"SPR_FNM1\", \"SPR_SURN\"\n",
    "  FROM \"INS_SPR\"\n",
    "    WHERE \"SPR_CODE\"='50200100';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Find the modules studied by a student\n",
    "\n",
    "**Show the module code and module name for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>MOD_CODE</th>\n",
       "        <th>MOD_NAME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CSN08101</td>\n",
       "        <td>Systems and Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INF08104</td>\n",
       "        <td>Database Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SET08108</td>\n",
       "        <td>Software Development 2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CSN08101', 'Systems and Services'),\n",
       " ('INF08104', 'Database Systems'),\n",
       " ('SET08108', 'Software Development 2')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"CAM_SMO\".\"MOD_CODE\", \"INS_MOD\".\"MOD_NAME\"\n",
    "  FROM \"INS_MOD\" JOIN \"CAM_SMO\" ON (\"INS_MOD\".\"MOD_CODE\"=\"CAM_SMO\".\"MOD_CODE\")\n",
    "    WHERE \"CAM_SMO\".\"SPR_CODE\"='50200100'\n",
    "    AND \"CAM_SMO\".\"AYR_CODE\"='2016/7'\n",
    "    AND \"CAM_SMO\".\"PSL_CODE\"='TR1';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Find the modules and module leader studied by a student\n",
    "\n",
    "**Show the module code and module name and details of the module leader for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>MOD_CODE</th>\n",
       "        <th>MOD_NAME</th>\n",
       "        <th>PRS_CODE</th>\n",
       "        <th>PRS_FNM1</th>\n",
       "        <th>PRS_SURN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CSN08101</td>\n",
       "        <td>Systems and Services</td>\n",
       "        <td>40000008</td>\n",
       "        <td>James</td>\n",
       "        <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>INF08104</td>\n",
       "        <td>Database Systems</td>\n",
       "        <td>40000036</td>\n",
       "        <td>Andrew</td>\n",
       "        <td>Cumming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SET08108</td>\n",
       "        <td>Software Development 2</td>\n",
       "        <td>40000408</td>\n",
       "        <td>Neil</td>\n",
       "        <td>Urquhart</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CSN08101', 'Systems and Services', '40000008', 'James', 'Jackson'),\n",
       " ('INF08104', 'Database Systems', '40000036', 'Andrew', 'Cumming'),\n",
       " ('SET08108', 'Software Development 2', '40000408', 'Neil', 'Urquhart')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"CAM_SMO\".\"MOD_CODE\", \"INS_MOD\".\"MOD_NAME\",\n",
    "       \"INS_PRS\".\"PRS_CODE\", \"INS_PRS\".\"PRS_FNM1\", \"INS_PRS\".\"PRS_SURN\"\n",
    "    FROM \"CAM_SMO\" JOIN \"INS_MOD\" ON (\"INS_MOD\".\"MOD_CODE\"=\"CAM_SMO\".\"MOD_CODE\")\n",
    "            JOIN \"INS_PRS\" ON (\"INS_MOD\".\"PRS_CODE\"=\"INS_PRS\".\"PRS_CODE\")\n",
    "        WHERE \"CAM_SMO\".\"SPR_CODE\"='50200100'\n",
    "        AND \"CAM_SMO\".\"AYR_CODE\"='2016/7'\n",
    "        AND \"CAM_SMO\".\"PSL_CODE\"='TR1';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Show the scores for module SET08108\n",
    "\n",
    "**Show the Percentage of students who gave 4 or 5 to module SET08108 in session 2016/7 TR1**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUE_CODE</th>\n",
       "        <th>QUE_TEXT</th>\n",
       "        <th>CAT_NAME</th>\n",
       "        <th>score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.1</td>\n",
       "        <td>Staff are good at explaining things.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.2</td>\n",
       "        <td>Staff made the subject interesting.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.3</td>\n",
       "        <td>The module was intellectually stimulating.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.4</td>\n",
       "        <td>The aims and objectives were clearly stated.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.5</td>\n",
       "        <td>The module was well-organised and ran smoothly.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.6</td>\n",
       "        <td>The pace was appropriate.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.7</td>\n",
       "        <td>The level was appropriate.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.8</td>\n",
       "        <td>The workload was managable.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1.9</td>\n",
       "        <td>I was able to contact module staff when I needed to.</td>\n",
       "        <td>Learning and Teaching</td>\n",
       "        <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.1</td>\n",
       "        <td>The assessment requirements were clearly stated.</td>\n",
       "        <td>Assessment and Feedback</td>\n",
       "        <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.2</td>\n",
       "        <td>The criteria for marking was made clear to me.</td>\n",
       "        <td>Assessment and Feedback</td>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.3</td>\n",
       "        <td>I was well supported for the assessment.</td>\n",
       "        <td>Assessment and Feedback</td>\n",
       "        <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.4</td>\n",
       "        <td>I was provided with feedback that aided understanding.</td>\n",
       "        <td>Assessment and Feedback</td>\n",
       "        <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.5</td>\n",
       "        <td>Feedback was provided within three weeks of submission.</td>\n",
       "        <td>Assessment and Feedback</td>\n",
       "        <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3.1</td>\n",
       "        <td>The module was well supported by moodle.</td>\n",
       "        <td>Learning Resources</td>\n",
       "        <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3.2</td>\n",
       "        <td>The library resources met my needs.</td>\n",
       "        <td>Learning Resources</td>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3.3</td>\n",
       "        <td>The IT resources met my needs.</td>\n",
       "        <td>Learning Resources</td>\n",
       "        <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3.4</td>\n",
       "        <td>The rooms/facilities were of good quality.</td>\n",
       "        <td>Learning Resources</td>\n",
       "        <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4.1</td>\n",
       "        <td>I was statisfied with the module.</td>\n",
       "        <td>Overall</td>\n",
       "        <td>89.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('1.1', 'Staff are good at explaining things.', 'Learning and Teaching', 89.0),\n",
       " ('1.2', 'Staff made the subject interesting.', 'Learning and Teaching', 82.0),\n",
       " ('1.3', 'The module was intellectually stimulating.', 'Learning and Teaching', 82.0),\n",
       " ('1.4', 'The aims and objectives were clearly stated.', 'Learning and Teaching', 89.0),\n",
       " ('1.5', 'The module was well-organised and ran smoothly.', 'Learning and Teaching', 78.0),\n",
       " ('1.6', 'The pace was appropriate.', 'Learning and Teaching', 80.0),\n",
       " ('1.7', 'The level was appropriate.', 'Learning and Teaching', 82.0),\n",
       " ('1.8', 'The workload was managable.', 'Learning and Teaching', 78.0),\n",
       " ('1.9', 'I was able to contact module staff when I needed to.', 'Learning and Teaching', 76.0),\n",
       " ('2.1', 'The assessment requirements were clearly stated.', 'Assessment and Feedback', 84.0),\n",
       " ('2.2', 'The criteria for marking was made clear to me.', 'Assessment and Feedback', 82.0),\n",
       " ('2.3', 'I was well supported for the assessment.', 'Assessment and Feedback', 80.0),\n",
       " ('2.4', 'I was provided with feedback that aided understanding.', 'Assessment and Feedback', 80.0),\n",
       " ('2.5', 'Feedback was provided within three weeks of submission.', 'Assessment and Feedback', 80.0),\n",
       " ('3.1', 'The module was well supported by moodle.', 'Learning Resources', 73.0),\n",
       " ('3.2', 'The library resources met my needs.', 'Learning Resources', 82.0),\n",
       " ('3.3', 'The IT resources met my needs.', 'Learning Resources', 89.0),\n",
       " ('3.4', 'The rooms/facilities were of good quality.', 'Learning Resources', 78.0),\n",
       " ('4.1', 'I was statisfied with the module.', 'Overall', 89.0)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"INS_RES\".\"QUE_CODE\", \"QUE_TEXT\", \"CAT_NAME\",\n",
    "       ROUND(100*SUM(FLOOR(\"RES_VALU\"/4))/COUNT(1)) AS score\n",
    "    FROM \"INS_RES\" JOIN \"INS_QUE\" ON \"INS_RES\".\"QUE_CODE\"=\"INS_QUE\".\"QUE_CODE\"\n",
    "               JOIN \"INS_CAT\" ON \"INS_QUE\".\"CAT_CODE\"=\"INS_CAT\".\"CAT_CODE\"\n",
    "        WHERE \"INS_RES\".\"MOD_CODE\"='SET08108'\n",
    "        AND \"INS_RES\".\"AYR_CODE\"='2016/7'\n",
    "        AND \"INS_RES\".\"PSL_CODE\"='TR1'\n",
    "        GROUP BY \"INS_RES\".\"QUE_CODE\", \"QUE_TEXT\", \"CAT_NAME\"\n",
    "        ORDEr BY \"INS_RES\".\"QUE_CODE\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Show the frequency chart for module SET08108 for question 4.1\n",
    "\n",
    "**For each response 1-5 show the number of students who gave that response (Module SET08108, 2016/7, TR1)**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>MOD_CODE</th>\n",
       "        <th>RES_VALU</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SET08108</td>\n",
       "        <td>2</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SET08108</td>\n",
       "        <td>4</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SET08108</td>\n",
       "        <td>5</td>\n",
       "        <td>39</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SET08108', 2, 6), ('SET08108', 4, 10), ('SET08108', 5, 39)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"MOD_CODE\", \"RES_VALU\", COUNT(1)\n",
    "  FROM \"INS_RES\"\n",
    "    WHERE \"INS_RES\".\"MOD_CODE\" = 'SET08108'\n",
    "    AND \"INS_RES\".\"AYR_CODE\"='2016/7'\n",
    "    AND \"INS_RES\".\"PSL_CODE\"='TR1'\n",
    "    AND \"INS_RES\".\"QUE_CODE\"='4.1'\n",
    "GROUP BY \"MOD_CODE\", \"RES_VALU\";"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
